package lk.uom.cse.sac.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import lk.uom.cse.sac.model.User;



public class UserDAO {
	
	private Statement stat;
	private Connection conn;
	
	
	private void initilized() throws SQLException, ClassNotFoundException{
		Class.forName("org.h2.Driver");
        conn = DriverManager.getConnection("jdbc:h2:db/db.epid", "admin", "admin");
        stat = conn.createStatement();
	}
	
	private void close() throws SQLException{
		 conn.close();
	}
	
	public User getUser(final Long userId){
		try{
			initilized();			
	        ResultSet resultset;
	        resultset = stat.executeQuery("SELECT * FROM USER WHERE ID = '"+(int) (long) userId+"'");
	        resultset.next();
	        User u = resultToUser(resultset);
	        resultset.close();
			close();
			return u;
		}catch(Exception ex){
			
		}
		return null;
	}
	
	public User saveUser(final User user){
		try{
			initilized();			
			stat.execute("INSERT INTO USER(USERNAME,PASSWORD,FULLNAME) VALUES('"+user.getUserName()+"','"+user.getPassword()+"','"+user.getFullName()+"')");
			ResultSet resultset;
			resultset = stat.executeQuery("SELECT * FROM USER WHERE USERNAME = '"+ user.getUserName()+"'");
			resultset.next();
	        User u = resultToUser(resultset);
	        resultset.close();
			close();
			return u;
		}catch(Exception ex){			
		}
		return user;
		
	}
	
	public void deleteUser(final User user){
		try{initilized();			
			stat.execute("DELETE FROM USER WHERE USERNAME = '"+user.getUserName()+"'");
			close();
		}catch(Exception ex){
		
		}	
	}
	
	
	public User updateUser(final User user){
		try{initilized();			
			stat.execute("UPDATE USER SET USERNAME='"+user.getUserName()+"',PASSWORD='"+user.getPassword()+"',FULLNAME='"+user.getFullName()+"' WHERE ID="+ user.getUserId() +";");
			close();
			ResultSet resultset;
			resultset = stat.executeQuery("SELECT * FROM USER WHERE USERNAME = '"+ user.getUserName()+"'");
			resultset.next();
	        User u = resultToUser(resultset);
	        resultset.close();
			close();
			return u;
		}catch(Exception ex){
			return user;
		}		
	}
	
	private User resultToUser(final ResultSet resultset) throws SQLException{
		User user= new User();
		user.setUserId((long) resultset.getInt(1));
		user.setUserName(resultset.getNString(2));
		user.setPassword(resultset.getNString(3));
		user.setFullName(resultset.getNString(4));
		user.setEmployeID(resultset.getNString(5));
		return user;
		
	}
	
	public User getUser(final String userName) {
		try{
			initilized();			
			ResultSet resultset;
			resultset = stat.executeQuery("SELECT * FROM USER WHERE USERNAME = '"+ userName+"'");
			resultset.next();
	        
	        User u = resultToUser(resultset);
	        resultset.close();
			close();
			return u;
		}catch(Exception ex){
			return null;
		}	
		
	}

	public void deleteAll() {
		try{
			initilized();			
			stat.execute("DELETE FROM USER");
			close();
		}catch(Exception ex){
		
		}
	}
}
